# !/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2021/2/8 9:54
# @Author  : qiaoc
# @File    : OcrUtils.py
# @Desc    : OCR识别操作类

import re
import openpyxl
from dao.PyMysql import *
from ocr.OcrDataProcess import *
import time

class OcrUtils:
    """
        移动电费票据OCR识别模块
    """

    def __init__(self, methodName):
        pass

    # 呼和浩特1 (最新版改为表格解析)
    def OCR_type_1(data_list, filename, bill_number, title_type):
        # print("使用类型：锡盟1")
        for data_num in range(len(data_list)):
            start_date = ''
            end_date = ''
            ele_in = ''
            uname = ''
            ele_loss = 0.00
            ele_price = ''
            total_power = ''
            ele_charge = ''
            last_display_num = 0.00
            now_display_num = 0.00
            uid = ''
            p_rule = re.compile(r'[(](.*?)[)]', re.S)  # 取括号内容
            p2_rule = re.compile(r'(.*?)\n起止时间', re.S)  # 取括号内容
            try:
                insert_list = []
                wb = openpyxl.Workbook()
                data = data_list[data_num]
                table_sum = 0
                for number in range(len(data["data"]["tables"])):
                    wb.create_sheet('table' + str(number + 1))
                    table_sum += 1
                    sh = wb['table' + str(number + 1)]
                    for i in data["data"]["tables"][number]["cells"]:
                        sh.cell(row=i["start_row"] + 1, column=i["start_col"] + 1,
                                value=(i["content"]).replace("|", "").replace(" ", ""))
                        next_row = False  # 是否去下一行内容(用于上期指示/本期指示)
                        for content in i["content"].split('\n'):
                            content = content.replace('：', ':')
                            # print(content)
                            # uid 用户编号/电表户号
                            try:
                                content = content.replace("（", "(").replace("）", ")")
                                if '(' in content and ')' in content:
                                    value_list = re.findall(p_rule, content)
                                    if len(value_list) == 1:
                                        if str(value_list[0]).isdecimal():
                                            uid = value_list[0]
                            except Exception as e:
                                pass
                            # start_date 开始日期/缴费期始    end_date  结束日期/缴费期终
                            try:
                                if '起止时间' in content or '时间' in content:
                                    content = str(content.replace(":", "").replace(" ", "")).split('时间')[1]
                                    value_list = content.split('到')
                                    # value_list = str(content.replace(":","").replace(" ","").replace("起止时间 ","").replace("起止时间","").replace("时间","")).split('到')
                                    if len(value_list) >= 2:
                                        start_date = value_list[0]
                                        end_date = value_list[1]
                                    if last_display_num == 0.0 and now_display_num == 0.0:
                                        rule_value = re.findall(p2_rule, i["content"])
                                        if len(rule_value) > 0:
                                            value_list = rule_value[0].split()
                                            if len(value_list) == 3 or len(value_list) == 4:
                                                if str(value_list[1].replace('.', '')).isdecimal():
                                                    last_display_num = value_list[1]
                                                if str(value_list[2].replace('.', '')).isdecimal():
                                                    if len(value_list[2].split('.')) == 3:
                                                        now_display_num = value_list[2].split('.')[0] + '.' + \
                                                                          value_list[2].split('.')[1]
                                                    else:
                                                        now_display_num = value_list[2]
                            except Exception as e:
                                pass
                            # last_display_num 上期指示 now_display_num 本期指示
                            try:
                                if '上期指示' in content or '本期指示' in content or '本期' in content or '上期' in content:
                                    next_row = True
                                    continue
                                if next_row == True:
                                    next_row = False
                                    value_list = content.split()
                                    if len(value_list) == 3 or len(value_list) == 4:
                                        if str(value_list[1].replace('.', '')).isdecimal():
                                            last_display_num = value_list[1]
                                        if str(value_list[2].replace('.', '')).isdecimal():
                                            if len(value_list[2].split('.')) == 3:
                                                now_display_num = value_list[2].split('.')[0] + '.' + \
                                                                  value_list[2].split('.')[1]
                                            else:
                                                now_display_num = value_list[2]
                            except Exception as e:
                                pass
                            # total_power 合计电量/总电量 ele_price 电价
                            try:
                                if '工商业及' in content:
                                    content = content.replace('工商业及 ', '').replace('工商业及', '')
                                    value_list = content.split()
                                    if len(value_list) >= 2:
                                        if str(value_list[0].replace('.', '')).isdecimal():
                                            if total_power == '':
                                                total_power = value_list[0]
                                            else:
                                                total_power = str(float(value_list[0]) + float(total_power))
                                        if str(value_list[1].replace('.', '')).isdecimal():
                                            if ele_price == '':
                                                ele_price = value_list[1]
                                            else:
                                                ele_price = ele_price + ';' + value_list[1]
                            except Exception as e:
                                pass
                            # ele_charge 电费金额
                            try:
                                if '电费合计' in content:
                                    content = content.replace('电费合计: ', '').replace('电费合计:', '').replace('电费合计', '')
                                    value_list = content.split()
                                    for value in value_list:
                                        if str(value.replace('.', '')).isdecimal():
                                            ele_charge = value
                                        else:
                                            value = str(content.replace(' ', '')).split('(')[0]
                                            if str(value.replace('.', '')).isdecimal():
                                                ele_charge = value
                                # if '本次缴费' in content:
                                #     content = content.replace('本次缴费: ','').replace('本次缴费:','').replace('本次缴费','')
                                #     value_list = content.split()
                                #     for value in value_list:
                                #         if str(value.replace('.','')).isdecimal() and ele_charge == '':
                                #             ele_charge = value
                            except Exception as e:
                                pass
                            # ele_loss 电损值
                            try:
                                if '损失电量' in content:
                                    content = content.replace('损失电量 ', '').replace('损失电量', '')
                                    value_list = content.split()
                                    for value in value_list:
                                        if str(value.replace('.', '')).isdecimal():
                                            ele_loss = value
                            except Exception as e:
                                pass
            except Exception as e:
                pass
            finally:
                key = ['uid', 'ele_price', 'total_power', 'last_display_num', 'now_display_num', 'start_date',
                       'end_date',
                       'ele_in', 'uname', 'ele_charge', 'affixx_name', 'bill_number', 'ele_loss', 'title_type']
                insert_list.append(
                    [uid, ele_price, total_power, last_display_num, now_display_num, start_date, end_date, ele_in,
                     uname,
                     ele_charge, filename, bill_number, ele_loss, title_type])
                PyMysql().insert_data(PyMysql().bill_table_name, key, insert_list)
                wb.close()
        return True

    # 呼和浩特2
    def OCR_type_2(result_ocr, filename, bill_number, title_type):
        # print("使用类型：呼和浩特2")
        value_list = []  # 值存储
        result_list = []  # 识别内容信息存储
        insert_list = []  # 储存插入数据库数据的二维数组
        for one_result in result_ocr:
            column = one_result['data']['tables'][0]['column']  # 获取表格列数
            column_second = column - 1
            row = one_result['data']['tables'][0]['row']  # 获取表格行数
            one_result = one_result['data']['tables'][0]['cells']  # 获取关键字结果
            for result in one_result:  # 识别结果整理成列表
                result['content'] = result['content'].replace(' ', '').replace('|', '').replace('\n', '')
                result_list.append(result['content'])
            result_list = result_list[column:]
            for i in range(1, row - 1):  # 获取value值:list
                if i == 1:
                    value = result_list[:column]
                    column = column + column_second
                    name = value[2]  # 获取户名，合并列值只有一个
                else:
                    value = result_list[column - column_second:column]
                    value.insert(2, name)  # 填充被合并丢失的值
                    column = column + column_second  # 第二行开始，第三列开始合并
                value_list.append(value)
            for value in value_list:
                value[4] = value[4].replace('.', '-')
                insert_list_part = [value[1], value[5], value[4], value[6], value[7], value[8], filename, bill_number,
                                    title_type]
                insert_list.append(insert_list_part)
        key = ['uid', 'uname', 'ele_in', 'total_power', 'ele_price', 'ele_charge', 'affixx_name', 'bill_number',
               'title_type']
        PyMysql().insert_data(PyMysql().bill_table_name, key, insert_list)
        return True

    # 呼和浩特3
    def OCR_type_3(data, filename, bill_number, title_type):
        # print("使用类型：呼和浩特3")
        result_list = []  # 识别内容信息存储
        value_list = []  # 值存储
        insert_list = []  # 储存插入数据库数据的二维数组
        num = []  # 存放户号,用于去重
        insert_list_second = []  # 储存插入数据库数据的二维数组(过滤掉重复数据)
        insert_list_part = []  # 用于临时存储，同一户号，多行指数相加
        column = data[0]['data']['tables'][0]['column']  # 获取表格列数
        column_second = column - 1
        row = data[0]['data']['tables'][0]['row']  # 获取表格行数
        result_ocr = data[0]['data']['tables'][0]['cells']  # 获取关键字结果
        for result in result_ocr:  # 识别结果整理成列表
            result['content'] = result['content'].replace(' ', '').replace('|', '').replace('\n', '')
            result_list.append(result['content'])
        result_list = result_list[column:]
        for i in range(1, row - 1):  # 获取value值:list
            if i == 1:
                value = result_list[:column]
                column = column + column_second
                date = value[3]  # 缴费区间，合并列值只有一个
            else:
                value = result_list[column - column_second:column]
                value.insert(3, date)  # 填充被合并丢失的值
                value_second = result_list[column:column + column_second]  # 下一行的值
                if '呼和浩特' not in value_second[1]:  # 一个户号，电指数出现多行的情况
                    column = column + 2
                else:
                    column = column + column_second
            value_list.append(value)
        for value in value_list:
            if '呼和浩特' in value[1]:
                insert_list_part = [value[1], value[2], value[3], value[4], value[5], value[7], value[8], value[9],
                                    value[10], filename]
                insert_list.append(insert_list_part)
            else:  # 同一户号，多行指数相加
                insert_list_part[3] = int(insert_list_part[3]) + int(value[-2])
                insert_list_part[4] = int(insert_list_part[4]) + int(value[-1])
                insert_list.append(insert_list_part)
        for data_list in insert_list:  # 去重
            if data_list[1] in num:
                continue
            else:
                num.append(data_list[1])
                insert_list_second.append(data_list)
        for value in insert_list_second:
            value[5] = re.findall(r"\d+\.?\d*", value[5])[0]  # 只取数字，防止印章影响
            value[6] = re.findall(r"\d+\.?\d*", value[6])[0]  # 只取数字，防止印章影响
            date = re.findall(r"\d{4}.\d{2}.\d{2}", value[2])  # 分割日期
            value[2] = date[0].replace('.', '-')
            date[1] = date[1].replace('.', '-')
            value.insert(3, date[1])
            value.append(bill_number)
            value.append(title_type)
        key = ['uname', 'uid', 'start_date', 'end_date', 'last_display_num', 'now_display_num', 'ele_loss',
               'total_power',
               'ele_price', 'ele_charge', 'affixx_name', 'bill_number', 'title_type']
        PyMysql().insert_data(PyMysql().bill_table_name, key, insert_list_second)
        return True

    # 锡盟1
    def OCR_type_4(data_list, filename, bill_number, title_type):
        # print("使用类型：锡盟1")
        for data_num in range(len(data_list)):
            try:
                insert_list = []
                wb = openpyxl.Workbook()
                data = data_list[data_num]
                table_sum = 0
                for number in range(len(data["data"]["tables"])):
                    wb.create_sheet('table' + str(number + 1))
                    table_sum += 1
                    sh = wb['table' + str(number + 1)]
                    for i in data["data"]["tables"][number]["cells"]:
                        sh.cell(row=i["start_row"] + 1, column=i["start_col"] + 1,
                                value=(i["content"]).replace("|", "").replace(" ", ""))
                        # print("内容：",i["content"],"行：",i["start_row"] + 1,"列：",i["start_col"] + 1)
                # wb.save(r"C:\Users\liuzhiqiang2_t\Desktop\test.xlsx")
                # 缴费单编号 bill_number
                # 附件名称 affixx_name
                # 页面类型 title_type
                # 开始日期 结束日期 电费月份系统中录入的分摊比例不正确
                start_date = ''
                end_date = ''
                ele_in = ''
                uname = ''
                ele_loss = 0.00
                ele_price = ''
                total_power = ''
                ele_charge = ''
                last_display_num = 0.00
                now_display_num = 0.00
                uid = ''
                try:
                    # print(data["data"]['items'])
                    for i in range(len(data["data"]['items'])):
                        if '（' in data["data"]['items'][i]['content'] and '）' in data["data"]['items'][i][
                            'content'] and '20' in data["data"]['items'][i]['content']:
                            ms = str(data["data"]['items'][i]['content']).split('（')[1]
                            s_e_d = ms.split('至')
                            start_date = str(s_e_d[0]).replace(' ', '')
                            end_date = str(s_e_d[1]).split('）')[0]
                            ele_in = (str(data["data"]['items'][i]['content'])).replace(' ', '').split('（')[0][-6:]
                            ele_in = ele_in[:4] + '-' + ele_in[4:]
                        elif '(' in data["data"]['items'][i]['content'] and ')' in data["data"]['items'][i][
                            'content'] and '20' in data["data"]['items'][i]['content']:
                            ms = str(data["data"]['items'][i]['content']).split('(')[1]
                            s_e_d = ms.split('至')
                            start_date = str(s_e_d[0]).replace(' ', '')
                            end_date = str(s_e_d[1]).split(')')[0]
                            ele_in = (str(data["data"]['items'][i]['content'])).replace(' ', '').split('(')[0][-6:]
                            ele_in = ele_in[:4] + '-' + ele_in[4:]
                        elif ')' in data["data"]['items'][i]['content'] and '20' in data["data"]['items'][i]['content'] \
                                and '至' in data["data"]['items'][i]['content']:
                            ms = str(data["data"]['items'][i]['content']).split('至')
                            end_date = ms[1].replace(')', '').replace('）', '')
                            start_date = ms[0].replace(' ', '')[-10:]
                            ele_in = ms[0].replace(' ', '')[-16:-10]
                            ele_in = ele_in[0:4] + '-' + ele_in[4:]
                            if '-' not in end_date or len(end_date) != 10:
                                end_date = ''
                            if '-' not in start_date or len(start_date) != 10:
                                start_date = ''
                            if len(ele_in) != 7:
                                ele_in = ''
                    # print("开始日期：", start_date)
                    # print("结束日期：", end_date)
                    # print("电费月份：", ele_in)
                except:
                    pass

                # 处理客户地址 客户编号
                excel_code = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R',
                              'S',
                              'T', 'U', 'V', 'W', 'X', 'Y', 'Z']
                # 多个sheet遍历
                for sheet in range(1, table_sum + 1):
                    sh = wb['table' + str(sheet)]
                    try:
                        for code in excel_code:
                            if '客户编号' in str(sh[code + str(1)].value):
                                num = int(excel_code.index(code)) + 1
                                excel_part_code = excel_code[num:]
                                for part in excel_part_code:
                                    if '客户地' in str(sh[part + str(1)].value):
                                        break
                                    if str(sh[part + str(1)].value).replace("\n", "") != 'None':
                                        uid = sh[part + str(1)].value.replace("\n", "").replace(" ", "")
                                        break
                            if '客户地' in str(sh[code + str(1)].value):
                                num = int(excel_code.index(code)) + 1
                                excel_part_code = excel_code[num:]
                                for part in excel_part_code:
                                    if str(sh[part + str(1)].value).replace("\n", "") != 'None':
                                        uname = sh[part + str(1)].value.replace("\n", "").replace(" ", "")
                                        break
                        # print("客户编号：", uid)
                        # print("客户地址：", uname)
                    except:
                        pass
                    # 默认【有功变损】在第14行,row为需要下移多少行,处理本月示数和上月示数
                    try:
                        row = 0
                        row_i = '14'
                        for i in range(1, sh.max_row):
                            if '有功' in str(sh["A" + str(i)].value):  # 有功变损
                                row = i - 14
                                # print("【有功变损】字段在第",str(i),"行")
                                row_i = str(i)  # 有功电损所在行
                                month_i = (int(row_i) - 1)
                                break
                        for i in range(1, sh.max_row):
                            if '表号' in str(sh["A" + str(i)].value) or '农号' in str(sh["A" + str(i)].value):
                                # print("【表号】字段在第",str(i),"行")
                                table_i = str(i)  # 有功电损所在行
                                # 上月 F13~  本月 G13~
                                num = 0
                                for code in excel_code:
                                    if sh[code + str(table_i)].value == '有功指示' or sh[
                                        code + str(table_i)].value == '有功指小':
                                        num = int(excel_code.index(code)) + 1
                                        excel_part_code = excel_code[num:]
                                        next_code = excel_code[num]
                                        break
                                range_row = int(month_i) - int(table_i) - 1
                                for i in range(range_row):
                                    # print(sh[code + str(month_i + i)].value.replace("\n", "").replace(" ", ""))
                                    if None != sh[code + str(month_i - i)].value:
                                        if (sh[code + str(month_i - i)].value) == '':
                                            continue
                                        last_display_num = last_display_num + float(
                                            sh[code + str(month_i - i)].value.replace("\n", "").replace(" ",
                                                                                                        "").replace("／",
                                                                                                                    "").replace(
                                                " ", ""))
                                    if None != sh[next_code + str(month_i - i)].value:
                                        if (sh[next_code + str(month_i - i)].value) == '':
                                            continue
                                        now_display_num = now_display_num + float(
                                            sh[next_code + str(month_i - i)].value.replace("\n", "").replace(" ",
                                                                                                             "").replace(
                                                "／", "").replace(" ", ""))
                                if now_display_num == 0.00:
                                    for j in range(num, 24):
                                        num += 1
                                        next_code = excel_code[num]
                                        if None != sh[next_code + str(month_i - i)].value:
                                            if (sh[next_code + str(month_i - i)].value) != '':
                                                now_display_num = now_display_num + float(
                                                    sh[next_code + str(month_i - i)].value.replace("\n", "").replace(
                                                        " ", "").replace("／", "").replace(" ", ""))
                                                break
                                # print('本月', now_display_num)
                                # print('上月', last_display_num)
                    except Exception as a:
                        pass
                        # print(a, ' ', str(a.__traceback__.tb_lineno))

                    # 有功变损 B14  + 有功线损 H14 = 电损值    处理电损值
                    ele_y = ''
                    ele_n = ''
                    try:
                        for code in excel_code:
                            if '有功变损' in str(sh[code + row_i].value).replace("\n", "") or '有功变捌' in str(
                                    sh[code + row_i].value).replace("\n", "") or '有功支损' in str(
                                    sh[code + row_i].value).replace("\n", "") or '有功变' in str(
                                    sh[code + row_i].value).replace("\n", ""):
                                num = int(excel_code.index(code)) + 1
                                excel_part_code = excel_code[num:]
                                for part in excel_part_code:
                                    if '无功变损' in str(sh[part + row_i].value):
                                        break
                                    if sh[part + row_i].value != None:
                                        if sh[part + row_i].value.replace("\n", "") != None:
                                            ele_y = sh[part + row_i].value.replace("\n", "").replace(" ", "").replace(
                                                "-", "").replace("／", "").replace(" ", "")
                                            break
                            if '有功线损' in str(sh[code + row_i].value).replace("\n", "") or '有功线捌' in str(
                                    sh[code + row_i].value).replace("\n", "") or '有功线' in str(
                                    sh[code + row_i].value).replace("\n", ""):
                                num = int(excel_code.index(code)) + 1
                                excel_part_code = excel_code[num:]
                                for part in excel_part_code:
                                    if '无功线损' in str(sh[part + row_i].value) or '无功线' in str(sh[part + row_i].value):
                                        break
                                    if sh[part + row_i].value != None:
                                        if sh[part + row_i].value.replace("\n", "") != None:
                                            ele_n = sh[part + row_i].value.replace("\n", "").replace(" ", "").replace(
                                                "-", "").replace("／", "").replace(" ", "")
                                            break
                        ele_loss = float(ele_y) + float(ele_n)
                        # print("电损值：", str(ele_loss))
                    except Exception as e:
                        pass
                        # print(e, ' ', str(e.__traceback__.tb_lineno))

                    # 电价（总和）ele_price 处理电价
                    try:
                        dictValue = {}
                        ele_code_list = []
                        code_second = 'J'
                        for i in range(1, sh.max_row):
                            if '项目' in str(sh["A" + str(i)].value) or '项月' in str(
                                    sh["A" + str(i)].value) or '项日' in str(
                                    sh["A" + str(i)].value):
                                # print("【电价】字段在第",str(i),"行")
                                ele_price_i = str(i)  # 电价所在行
                                break
                        for code in excel_code:
                            if sh[code + ele_price_i].value == None:
                                continue
                            if sh[code + ele_price_i].value == '电价':
                                ele_code_list.append(code)
                            if '项目' in sh[code + ele_price_i].value or '项月' in sh[code + ele_price_i].value or '项日' in \
                                    sh[
                                        code + ele_price_i].value:
                                if code == 'A':
                                    continue
                                else:
                                    code_second = code
                        # 1
                        if "电价" == sh[ele_code_list[0] + str(19 + row - 1)].value:
                            dictValue[sh["A" + str(19 + row)].value.replace("\n", "").replace(" ", "").replace("／",
                                                                                                               "").replace(
                                " ", "")] = sh[
                                ele_code_list[0] + str(19 + row)].value.replace("\n", "").replace(" ", "").replace("／",
                                                                                                                   "").replace(
                                " ", "")
                        # print(sh["A" + str(19 + row)].value.replace("\n", "").replace(" ", ""), "：",sh[ele_code_list[0] + str(19 + row)].value.replace("\n", "").replace(" ", ""))
                        # 2
                        if "电价" == sh[ele_code_list[0] + str(19 + row - 1)].value:
                            dictValue[sh["A" + str(20 + row)].value.replace("\n", "").replace(" ", "").replace("／",
                                                                                                               "").replace(
                                " ", "")] = sh[
                                ele_code_list[0] + str(20 + row)].value.replace("\n", "").replace(" ", "").replace("／",
                                                                                                                   "").replace(
                                " ", "")
                        # print(sh["A" + str(20 + row)].value.replace("\n", "").replace(" ", ""), "：",
                        #       sh[ele_code_list[0] + str(20 + row)].value.replace("\n", "").replace(" ", ""))
                        # 3
                        if "电价" == sh[ele_code_list[0] + str(19 + row - 1)].value:
                            if "合计信息" not in sh["A" + str(21 + row)].value.replace("\n", "").replace(" ", "").replace(
                                    "／", "").replace(" ", "") and sh[
                                ele_code_list[0] + str(21 + row)].value is not None:
                                dictValue[sh["A" + str(21 + row)].value.replace("\n", "").replace(" ", "").replace("／",
                                                                                                                   "").replace(
                                    " ", "")] = sh[
                                    ele_code_list[0] + str(21 + row)].value.replace("\n", "").replace(" ", "").replace(
                                    "／", "").replace(" ", "")
                        # print(sh["A" + str(21 + row)].value.replace("\n", "").replace(" ", ""), "：",
                        #       sh[ele_code_list[0] + str(21 + row)].value.replace("\n", "").replace(" ", ""))

                        # 4
                        if "电价" == sh[ele_code_list[-1] + str(19 + row - 1)].value:
                            dictValue[
                                sh[code_second + str(19 + row)].value.replace("\n", "").replace(" ", "").replace("／",
                                                                                                                 "").replace(
                                    " ", "")] = sh[
                                ele_code_list[-1] + str(19 + row)].value.replace("\n", "").replace(" ", "").replace("／",
                                                                                                                    "").replace(
                                " ", "")
                        # print(sh[code_second + str(19 + row)].value.replace("\n", "").replace(" ", ""), "：",
                        #       sh[ele_code_list[-1] + str(19 + row)].value.replace("\n", "").replace(" ", ""))
                        # 5
                        if "电价" == sh[ele_code_list[-1] + str(19 + row - 1)].value:
                            dictValue[
                                sh[code_second + str(20 + row)].value.replace("\n", "").replace(" ", "").replace("／",
                                                                                                                 "").replace(
                                    " ", "")] = sh[
                                ele_code_list[-1] + str(20 + row)].value.replace("\n", "").replace(" ", "").replace("／",
                                                                                                                    "").replace(
                                " ", "")
                        # print(sh[code_second + str(20 + row)].value.replace("\n", "").replace(" ", ""), "：",
                        #       sh[ele_code_list[-1] + str(20 + row)].value.replace("\n", "").replace(" ", ""))
                        # 6
                        if "电价" == sh[ele_code_list[-1] + str(19 + row - 1)].value:
                            if None != sh[code_second + str(21 + row)].value and None != sh[
                                ele_code_list[-1] + str(21 + row)].value and '' != sh[
                                ele_code_list[-1] + str(21 + row)].value:
                                dictValue[
                                    sh[code_second + str(21 + row)].value.replace("\n", "").replace(" ", "").replace(
                                        "／", "").replace(" ", "")] = sh[
                                    ele_code_list[-1] + str(21 + row)].value.replace("\n", "").replace(" ", "").replace(
                                    "／", "").replace(" ", "")
                        #     print(sh[code_second + str(21 + row)].value.replace("\n", "").replace(" ", ""), "：",
                        #           sh[ele_code_list[-1] + str(21 + row)].value.replace("\n", "").replace(" ", ""))
                        # print(dictValue)
                        price1 = 0.000000
                        price2 = 0.000000
                        if len(dictValue) <= 5:
                            for key in dictValue.keys():
                                if '.' not in dictValue[key]:
                                    dictValue[key] = '0.' + dictValue[key]
                                price1 = float(dictValue[key]) + price1
                            # print("单价1：", str(price1))
                            ele_price = str(price1)
                            # print("电价：", ele_price)
                        elif len(dictValue) == 6:
                            for key in dictValue.keys():
                                if key in "工商业及其它(单一制)/1-10千伏":
                                    if '.' not in dictValue[key]:
                                        dictValue[key] = '0.' + dictValue[key]
                                    price1 = price1 + float(dictValue[key])
                                if key in "工商业及其它(两部制大工业新兴特色)/1-10千伏":
                                    if '.' not in dictValue[key]:
                                        dictValue[key] = '0.' + dictValue[key]
                                    price2 = price2 + float(dictValue[key])
                                if key in "重大水利基金":
                                    if '.' not in dictValue[key]:
                                        dictValue[key] = '0.' + dictValue[key]
                                    price1 = price1 + float(dictValue[key])
                                    price2 = price2 + float(dictValue[key])
                                if key in "库区移民":
                                    if '.' not in dictValue[key]:
                                        dictValue[key] = '0.' + dictValue[key]
                                    price1 = price1 + float(dictValue[key])
                                    price2 = price2 + float(dictValue[key])
                                if key in "可再生能源":
                                    if '.' not in dictValue[key]:
                                        dictValue[key] = '0.' + dictValue[key]
                                    price1 = price1 + float(dictValue[key])
                                    price2 = price2 + float(dictValue[key])
                                if key in "农网还贷资金":
                                    if '.' not in dictValue[key]:
                                        dictValue[key] = '0.' + dictValue[key]
                                    price1 = price1 + float(dictValue[key])
                            # print("单价1：", str(price1))
                            # print("单价2：", str(price2))
                            ele_price = str(price1) + ';' + str(price2)
                        # print("电价：", ele_price)
                    except Exception as e:
                        print(e, ' ', str(e.__traceback__.tb_lineno))
                        pass
                    try:
                        # 总电量 K15 total_power
                        for i in range(1, sh.max_row):
                            if '退补有' in str(sh["A" + str(i)].value):
                                # print("【退补有功电量】字段在第", str(i), "行")
                                ele_i = str(i)  # 总电量所在行
                                break
                        for code in excel_code:
                            if sh[code + ele_i].value == None:
                                continue
                            if '有功总电' in sh[code + ele_i].value.replace("\n", "") or '有功息电' in sh[
                                code + ele_i].value.replace("\n", ""):
                                # print(sh[code + ele_i].value)
                                num = int(excel_code.index(code)) + 1
                                excel_part_code = excel_code[num:]
                                for part in excel_part_code:
                                    if sh[part + ele_i].value != None:
                                        if sh[part + ele_i].value.replace("\n", "") != None:
                                            total_power = sh[part + ele_i].value.replace("\n", "").replace(" ", "")
                                            break
                        # print("电量：", total_power)
                    except:
                        pass
                    try:
                        # 电费 K25 ele_charge
                        for i in range(1, sh.max_row):
                            if '追退基' in str(sh["A" + str(i)].value):
                                # print("【追退基本】字段在第", str(i), "行")
                                ele_money_i = str(i)  # 电费所在行
                                break
                        for code in excel_code:
                            if sh[code + ele_money_i].value == None:
                                continue
                            if '金合计' in sh[code + ele_money_i].value:  # 资金合计
                                num = int(excel_code.index(code)) + 1
                                excel_part_code = excel_code[num:]
                                for part in excel_part_code:
                                    if sh[part + ele_money_i].value != None:
                                        if sh[part + ele_money_i].value.replace("\n", "") != None:
                                            ele_charge = sh[part + ele_money_i].value.replace("\n", "").replace(" ", "")
                                            break
                        # print("电费：", ele_charge)
                    except Exception as e:
                        # print(e, ' ', str(e.__traceback__.tb_lineno))
                        pass

            except Exception as e:
                # print(e, ' ', str(e.__traceback__.tb_lineno))
                pass
            finally:
                key = ['uid', 'ele_price', 'total_power', 'last_display_num', 'now_display_num', 'start_date',
                       'end_date', 'ele_in', 'uname', 'ele_charge', 'affixx_name', 'bill_number', 'ele_loss',
                       'title_type']
                insert_list.append(
                    [uid, ele_price, total_power, last_display_num, now_display_num, start_date, end_date, ele_in,
                     uname, ele_charge, filename, bill_number, ele_loss, title_type])
                PyMysql().insert_data(PyMysql().bill_table_name, key, insert_list)
                wb.close()
        return True

    # 锡盟3(模糊识别效率不高)
    def OCR_type_6(data, filename, bill_number, title_type):
        # print("使用类型：锡盟3")
        result_data = {}
        insert_list = []  # 返回的二维列表
        result_list = data[0]['data']['results']
        numbers = []  # 电量电价电费数值行（‘工商业及’后面的）
        magnification = []  # 倍率
        amount = []  # 电费合计
        liquidated_damages = []  # 违约金
        s_e_date = []  # 起止时间
        table_num = []  # 表号
        ele_loss = ''  # 电损值
        name_list = []  # 户名
        for field_result in result_list:
            field_name = field_result['field_name']
            field_result = field_result['results']
            # print(field_name, ' - ', field_result)
            if '倍率' in field_name:
                magnification = field_result
            if '工商业及' in field_name:
                numbers = field_result
            if '电费合计' in field_name:
                amount = field_result
            if '违约金' in field_name:
                liquidated_damages = field_result
            if '起止时间' in field_name:
                s_e_date = field_result
            if '损失电量' in field_name:
                if field_result:
                    ele_loss = field_result[0]
            if '表号' in field_name:
                table_num = field_result
            if '户名' in field_name:
                name_list = field_result
        if len(magnification) == 0:
            magnification.append('')
        if len(numbers) == 0:
            numbers.append('')
        if len(amount) == 0:
            amount.append('')
        if len(liquidated_damages) == 0:
            liquidated_damages.append('')
        if len(s_e_date) == 0:
            s_e_date.append('')
        if len(name_list) == 0:
            name_list.append('')
        account_number = ''  # 户号（对应单据中的表号）
        now_display_num = ''  # 本期指示
        last_display_num = ''  # 上期指示
        ele_price = ''  # 电价
        total_power = ''  # 电量
        account_name = ''  # 户名
        # 处理 电量、电价、户号、本期指示、上期指示
        price_numbers = []
        for n in numbers:
            price_numbers.extend(str(n).split())
        if len(price_numbers) == 7 or len(price_numbers) == 6:
            total_power = str(price_numbers[0])
            ele_price = str(price_numbers[1])
            account_number = str(price_numbers[3])
            last_display_num = str(price_numbers[4])
            now_display_num = str(price_numbers[5])
        elif len(price_numbers) == 5:
            total_power = str(price_numbers[0])
            ele_price = str(price_numbers[1])
            account_number = str(price_numbers[3])
            last_display_num = str(price_numbers[4])
            mag_list = []
            for n in magnification:
                mag_list.extend(str(n).split())
            if len(mag_list) >= 2:
                now_display_num = str(mag_list[-2])
        elif len(price_numbers) == 4:
            total_power = str(price_numbers[0])
            ele_price = str(price_numbers[1])
            account_number = str(price_numbers[3])
            mag_list = []
            for n in magnification:
                mag_list.extend(str(n).split())
            if len(mag_list) >= 3:
                now_display_num = str(mag_list[-2])
                last_display_num = str(mag_list[-3])
        elif len(price_numbers) == 3:
            total_power = str(price_numbers[0])
            ele_price = str(price_numbers[1])
            mag_list = []
            for n in magnification:
                mag_list.extend(str(n).split())
            if len(mag_list) >= 4:
                now_display_num = str(mag_list[-2])
                last_display_num = str(mag_list[-3])
                account_number = str(mag_list[-4])
            else:
                now_display_num = str(table_num[0].split(' ')[2])
                last_display_num = str(table_num[0].split(' ')[1])
                # account_number = str(table_num[0])
        elif len(price_numbers) == 0:
            now_display_num = str(table_num[0].split(' ')[2])
            last_display_num = str(table_num[0].split(' ')[1])
            # account_number = str(table_num[0])
        # 处理户号、户名
        try:
            if name_list:
                for name in name_list:
                    if '(' in name:
                        if not re.findall(r"\d+\.?\d*", name):
                            continue
                        if len(re.findall(r"\d+\.?\d*", name)[0]) < 4:
                            continue
                        name1 = name.split('(')
                        account_number = name1[-1].replace(')', '')
                        i = name_list.index(name)
                        i = int(i) + 1
                        account_name = name_list[i]
                        if '付款' in account_name:
                            i = i + 1
                            account_name = name_list[i]
                        break
        except Exception as e:
            pass
        result_data['uname'] = account_name
        result_data['account_number'] = account_number
        result_data['ele_price'] = ele_price
        result_data['total_power'] = total_power
        result_data['last_display_num'] = last_display_num
        result_data['now_display_num'] = now_display_num
        result_data['ele_loss'] = ele_loss
        start_date = ''  # 开始日期
        end_date = ''  # 结束日期
        month = ''  # 电费月份
        # 处理 起止时间
        try:
            if len(s_e_date) >= 1:
                d_list = str(s_e_date[0]).split('到')
                start_date = str(d_list[0]).replace(".", "-")
                end_date = str(d_list[1]).replace(".", "-")
        except Exception as e:
            pass
        result_data['start_date'] = start_date
        result_data['end_date'] = end_date
        result_data['month'] = month
        ele_charge = ''  # 电费
        # 处理 电费
        try:
            if amount:
                amount_first = str(amount[0]).strip().split(' ')  # 可能含有(其中阶梯差额:0.00)
                if '(' in str(amount_first):
                    amount_first = str(amount_first).split('(')[0].strip()
                else:
                    amount_first = str(amount_first[0])
            if liquidated_damages:
                weiyuejin = str(liquidated_damages[0]).strip().split(' ')
                if weiyuejin:
                    ele_charge = str(float(amount_first) + float(weiyuejin[0]))
                else:
                    ele_charge = amount_first
        except Exception as e:
            pass
        result_data['ele_charge'] = ele_charge
        insert_list.append([result_data['account_number'], result_data['start_date'], result_data['end_date'],
                            result_data['last_display_num'],
                            result_data['now_display_num'], result_data['total_power'], result_data['ele_price'],
                            result_data['ele_charge'],
                            result_data['ele_loss'], filename, bill_number, title_type, result_data['uname']])
        key = ['uid', 'start_date', 'end_date', 'last_display_num', 'now_display_num', 'total_power', 'ele_price',
               'ele_charge', 'ele_loss', 'affixx_name', 'bill_number', 'title_type', 'uname']
        PyMysql().insert_data(PyMysql().bill_table_name, key, insert_list)
        return True

    # 锡盟4(模糊识别效率不高)
    def OCR_type_7(data, filename, bill_number, title_type):
        # print("使用类型：锡盟4")
        result_data = {}
        insert_list = []  # 返回的二维列表
        result_list = data[0]['data']['results']
        numbers = []  # 电量电价电费数值行（‘工商业及’后面的）
        magnification = []  # 倍率
        amount = []  # 电费合计
        liquidated_damages = []  # 违约金
        s_e_date = []  # 起止时间
        table_num = []  # 表号
        ele_loss = ''  # 电损值
        name_list = []  # 户名
        for field_result in result_list:
            field_name = field_result['field_name']
            field_result = field_result['results']
            # print(field_name, ' - ', field_result)
            if '倍率' in field_name:
                magnification = field_result
            if '工商业及' in field_name:
                numbers = field_result
            if '电费合计' in field_name:
                amount = field_result
            if '违约金' in field_name:
                liquidated_damages = field_result
            if '起止时间' in field_name:
                s_e_date = field_result
            if '损失电量' in field_name:
                if field_result:
                    ele_loss = field_result[0]
            if '表号' in field_name:
                table_num = field_result
            if '户名' in field_name:
                name_list = field_result
        if len(magnification) == 0:
            magnification.append('')
        if len(numbers) == 0:
            numbers.append('')
        if len(amount) == 0:
            amount.append('')
        if len(liquidated_damages) == 0:
            liquidated_damages.append('')
        if len(s_e_date) == 0:
            s_e_date.append('')
        if len(name_list) == 0:
            name_list.append('')
        account_number = ''  # 户号（对应单据中的表号）
        now_display_num = ''  # 本期指示
        last_display_num = ''  # 上期指示
        ele_price = ''  # 电价
        total_power = ''  # 电量
        account_name = ''  # 户名
        # 处理 电量、电价、户号、本期指示、上期指示
        price_numbers = []
        for n in numbers:
            price_numbers.extend(str(n).split())
        if len(price_numbers) == 7 or len(price_numbers) == 6:
            total_power = str(price_numbers[0])
            ele_price = str(price_numbers[1])
            account_number = str(price_numbers[3])
            last_display_num = str(price_numbers[4])
            now_display_num = str(price_numbers[5])
        elif len(price_numbers) == 5:
            total_power = str(price_numbers[0])
            ele_price = str(price_numbers[1])
            account_number = str(price_numbers[3])
            last_display_num = str(price_numbers[4])
            mag_list = []
            for n in magnification:
                mag_list.extend(str(n).split())
            if len(mag_list) >= 2:
                now_display_num = str(mag_list[-2])
        elif len(price_numbers) == 4:
            total_power = str(price_numbers[0])
            ele_price = str(price_numbers[1])
            account_number = str(price_numbers[3])
            mag_list = []
            for n in magnification:
                mag_list.extend(str(n).split())
            if len(mag_list) >= 3:
                now_display_num = str(mag_list[-2])
                last_display_num = str(mag_list[-3])
        elif len(price_numbers) == 3:
            total_power = str(price_numbers[0])
            ele_price = str(price_numbers[1])
            mag_list = []
            for n in magnification:
                mag_list.extend(str(n).split())
            if len(mag_list) >= 4:
                now_display_num = str(mag_list[-2])
                last_display_num = str(mag_list[-3])
                account_number = str(mag_list[-4])
            else:
                if len(str(table_num[2])) >= 8:
                    now_display_num = str(table_num[2])[:7]
                else:
                    now_display_num = str(table_num[2])
                last_display_num = str(table_num[1])
                account_number = str(table_num[0])
        # 处理户号、户名
        try:
            if name_list:
                for name in name_list:
                    if '(' in name:
                        if not re.findall(r"\d+\.?\d*", name):
                            continue
                        name1 = name.split('(')
                        account_number = name1[-1].replace(')', '')
                        i = name_list.index(name)
                        i = int(i) + 1
                        account_name = name_list[i]
                        if '付款' in account_name:
                            i = i + 1
                            account_name = name_list[i]
                        break
        except Exception as e:
            pass
        result_data['uname'] = account_name
        result_data['account_number'] = account_number
        result_data['ele_price'] = ele_price
        result_data['total_power'] = total_power
        result_data['last_display_num'] = last_display_num
        result_data['now_display_num'] = now_display_num
        result_data['ele_loss'] = ele_loss
        start_date = ''  # 开始日期
        end_date = ''  # 结束日期
        month = ''  # 电费月份
        # 处理 起止时间
        try:
            if len(s_e_date) >= 1:
                d_list = str(s_e_date[0]).split('到')
                start_date = d_list[0]
                end_date = d_list[1]
        except Exception as e:
            pass
        result_data['start_date'] = start_date
        result_data['end_date'] = end_date
        result_data['month'] = month
        ele_charge = ''  # 电费
        # 处理 电费
        if amount:
            amount_first = str(amount[0]).strip()  # 可能含有(其中阶梯差额:0.00)
            if '(' in str(amount_first):
                amount_first = str(amount_first).split('(')[0].strip()
        if liquidated_damages:
            weiyuejin = str(liquidated_damages[0]).strip()
            if weiyuejin:
                ele_charge = str(float(amount_first) + float(weiyuejin))
            else:
                ele_charge = amount_first
            result_data['ele_charge'] = ele_charge
        insert_list.append([result_data['account_number'], result_data['start_date'], result_data['end_date'],
                            result_data['last_display_num'],
                            result_data['now_display_num'], result_data['total_power'], result_data['ele_price'],
                            result_data['ele_charge'],
                            result_data['ele_loss'], filename, bill_number, title_type, result_data['uname']])
        key = ['uid', 'start_date', 'end_date', 'last_display_num', 'now_display_num', 'total_power', 'ele_price',
               'ele_charge', 'ele_loss', 'affixx_name', 'bill_number', 'title_type', 'uname']
        PyMysql().insert_data(PyMysql().bill_table_name, key, insert_list)
        pass

    # 锡盟5
    def OCR_type_8(data, filename, bill_number, title_type):
        # print("使用类型：锡盟5")
        title_list = ['序号', '站点名称 ', '用户号 ', '付费方式', '户名 ', '用户地址 ', '供电所 ', '购电起始读数', '购电截止读数', '倍率 ', '变损 ', '用电量 ',
                      '电费单价(元)', '优惠金额', '用电金额(元)', '购电抄表日期']
        db_title = ['uid', 'uname', 'ele_in', 'last_display_num', 'now_display_num', 'ele_loss', 'total_power',
                    'ele_price',
                    'ele_charge', 'affixx_name', 'bill_number', 'title_type']
        result_data = {}
        all_save_data = []  # 存到数据库中的所有数据
        month = ''
        regx = re.compile(r"\d{4}年\d{1,2}月")
        for one_data in data:
            column_count = one_data['data']['tables'][0]['column']  # 获取表格列数
            row_count = one_data['data']['tables'][0]['row']  # 获取表格行数
            table_cells = one_data['data']['tables'][0]['cells']
            all_row_data = []  # 所有行的数据
            one_row_data = []  # 每一行的数据
            row_index = 0
            for cell in table_cells:
                # print("row:",cell['start_row'],", col:",cell['start_col'],", content:",cell['content'])
                cell_row_index = int(cell['start_row'])
                if cell_row_index != row_index:
                    all_row_data.append(one_row_data)
                    one_row_data = []
                    row_index += 1
                one_row_data.append(cell['content'].replace(' ', '').replace('|', '').replace('\n', ''))
            all_row_data.append(one_row_data)
            # print(all_row_data)
            for row in all_row_data:
                if len(row) <= 14:
                    for m in row:
                        i = regx.search(str(m))
                        if i:
                            month = i.group()
                            month = month.split('年')
                            if len(month[-1].replace('月', '')) == 1:
                                month[-1] = '0' + month[-1].replace('月', '')
                            month = month[0] + '-' + month[-1]
                    continue
                title_flag = False
                for c in row:
                    if str(c) in title_list:
                        title_flag = True
                        break
                if not title_flag:
                    insert_list_part = [row[2], row[1], month, row[7], row[8], row[10], row[11], row[12], row[14],
                                        filename,
                                        bill_number, title_type]
                    all_save_data.append(insert_list_part)
        # print(all_save_data)
        PyMysql().insert_data(PyMysql().bill_table_name, db_title, all_save_data)
        return True

    # 锡盟11
    def OCR_type_11(data, filename, bill_number, title_type):
        for i in range(len(data)):
            # print("使用类型：锡盟11")
            result_data = {}
            insert_list = []  # 数据库插入的二维数组
            # print(data[0]['data'])
            result_list = data[i]['data']['results']
            numbers = []  # 电量电价电费数值行（‘工商业及’后面的）
            magnification = []  # 倍率
            amount = []  # 电费合计
            liquidated_damages = []  # 违约金
            s_e_date = []  # 起止时间
            ele_loss = ''  # 电损值
            account_name = ''  # 户名
            name_list = []  # 户名列表
            for field_result in result_list:
                field_name = field_result['field_name']
                field_result = field_result['results']
                # print(field_name, ' - ', field_result)
                if '倍率' in field_name:
                    magnification = field_result
                if '工商业及' in field_name:
                    numbers = field_result
                if '电费合计' in field_name:
                    amount = field_result
                if '违约金' in field_name:
                    liquidated_damages = field_result
                if '起止时间' in field_name:
                    s_e_date = field_result
                if '损失电量' in field_name:
                    if field_result:
                        ele_loss = field_result[0]
                if '户名' in field_name:
                    name_list = field_result
            if len(magnification) == 0:
                magnification.append('')
            if len(numbers) == 0:
                numbers.append('')
            if len(amount) == 0:
                amount.append('')
            if len(liquidated_damages) == 0:
                liquidated_damages.append('')
            if len(s_e_date) == 0:
                s_e_date.append('')
            if len(name_list) == 0:
                name_list.append('')
            account_number = ''  # 户号（对应单据中的表号）
            now_display_num = '0.0'  # 本期指示
            last_display_num = '0.0'  # 上期指示
            ele_price = ''  # 电价
            total_power = ''  # 电量
            # 处理 电量、电价、户号、本期指示、上期指示
            price_numbers = []
            for n in numbers:
                price_numbers.extend(str(n).split())
            if len(price_numbers) == 7 or len(price_numbers) == 6:
                total_power = str(price_numbers[0])
                ele_price = str(price_numbers[1])
                account_number = str(price_numbers[3])
                last_display_num = str(price_numbers[4])
                now_display_num = str(price_numbers[5])
            elif len(price_numbers) == 5:
                total_power = str(price_numbers[0])
                ele_price = str(price_numbers[1])
                account_number = str(price_numbers[3])
                last_display_num = str(price_numbers[4])
                mag_list = []
                for n in magnification:
                    mag_list.extend(str(n).split())
                if len(mag_list) >= 2:
                    now_display_num = str(mag_list[-2])
            elif len(price_numbers) == 4:
                total_power = str(price_numbers[0])
                ele_price = str(price_numbers[1])
                account_number = str(price_numbers[3])
                mag_list = []
                for n in magnification:
                    mag_list.extend(str(n).split())
                if len(mag_list) >= 3:
                    now_display_num = str(mag_list[-2])
                    last_display_num = str(mag_list[-3])
            elif len(price_numbers) == 3:
                total_power = str(price_numbers[0])
                ele_price = str(price_numbers[1])
                mag_list = []
                for n in magnification:
                    mag_list.extend(str(n).split())
                if len(mag_list) >= 4:
                    now_display_num = str(mag_list[-2])
                    last_display_num = str(mag_list[-3])
                    account_number = str(mag_list[-4])

            if str(ele_price.replace('.', '')).isdecimal():
                result_data['ele_price'] = ele_price
            else:
                result_data['ele_price'] = ''
            if str(total_power.replace('.', '')).isdecimal():
                result_data['total_power'] = total_power
            else:
                result_data['total_power'] = ''

            if str(last_display_num.replace('.', '')).isdecimal():
                if len(last_display_num.split('.')) > 2:
                    result_data['last_display_num'] = '0.0'
                else:
                    result_data['last_display_num'] = last_display_num
            else:
                result_data['last_display_num'] = '0.0'

            if str(now_display_num.replace('.', '')).isdecimal():
                if len(now_display_num.split('.')) > 2:
                    result_data['now_display_num'] = '0.0'
                else:
                    result_data['now_display_num'] = now_display_num
            else:
                result_data['now_display_num'] = '0.0'

            if str(ele_loss.replace('.', '')).isdecimal():
                result_data['ele_loss'] = ele_loss
            else:
                result_data['ele_loss'] = '0.0'
            data_list = []
            if result_data['last_display_num'] == '0.0' and result_data['now_display_num'] == '0.0':
                for n in magnification:
                    data_list.extend(str(n).split())
            if len(data_list) > 2:
                if str(data_list[1].replace('.', '')).isdecimal():
                    result_data['last_display_num'] = data_list[1]
                if str(data_list[2].replace('.', '')).isdecimal():
                    result_data['now_display_num'] = data_list[2]
            start_date = ''  # 开始日期
            end_date = ''  # 结束日期
            month = ''  # 电费月份
            # 处理户号、户名
            p_rule = re.compile(r'[(](.*?)[)]', re.S)  # 取括号内容
            try:
                if name_list:
                    for name in name_list:
                        name = name.replace('（', '(').replace('）', ')')
                        value_list = re.findall(p_rule, name)
                        if len(value_list) > 0:
                            account_number = value_list[0]
                        if '(' in name:
                            if not re.findall(r"\d+\.?\d*", name):
                                continue
                            name = name.split('(')
                            if ')' in name[1]:
                                account_name = name[0] + '(' + name[1]
                            else:
                                account_name = name[0]
                        break
            except Exception as e:
                pass
            # 处理起止时间
            try:
                if len(s_e_date) >= 1:
                    d_list = str(s_e_date[0]).split('到')
                    start_date = d_list[0]
                    end_date = d_list[1]
            except Exception as e:
                pass
            result_data['account_number'] = account_number
            result_data['uname'] = account_name
            result_data['start_date'] = start_date
            result_data['end_date'] = end_date
            result_data['month'] = month
            ele_charge = ''  # 电费
            # 处理 电费
            amount_first = str(amount[0]).strip()  # 可能含有(其中阶梯差额:0.00)
            if '(' in str(amount_first):
                amount_first = str(amount_first).split('(')[0].strip()
            weiyuejin = str(liquidated_damages[0]).strip()
            if weiyuejin:
                ele_charge = str(float(amount_first) + float(weiyuejin))
            else:
                ele_charge = amount_first
            result_data['ele_charge'] = ele_charge
            key = ['uid', 'ele_price', 'total_power', 'last_display_num', 'now_display_num', 'start_date', 'end_date',
                   'ele_in',
                   'ele_charge', 'affixx_name', 'bill_number', 'ele_loss', 'title_type', 'uname']
            insert_list.append([result_data['account_number'], result_data['ele_price'], result_data['total_power'],
                                result_data['last_display_num'], result_data['now_display_num'],
                                result_data['start_date'], result_data['end_date'],
                                result_data['month'], result_data['ele_charge'], filename, bill_number,
                                result_data['ele_loss'], title_type, result_data['uname']])
            PyMysql().insert_data(PyMysql().bill_table_name, key, insert_list)
        return True

    # 固话1
    def OCR_type_9(data, filename, bill_number, title_type):
        # print('固话类型')
        result_list = []  # 识别内容信息存储
        value_list = []  # 值存储
        column_start = 0
        for j in range(len(data)):
            try:
                column = data[j]['data']['tables'][0]['column']  # 获取表格列数
                # print(column)
                column_second = column
                row = data[j]['data']['tables'][0]['row']  # 获取表格行数
                result_ocr = data[j]['data']['tables'][0]['cells']  # 获取关键字结果
                # print(result_ocr)
                for result in result_ocr:  # 识别结果整理成列表
                    result['content'] = result['content'].replace(' ', '').replace('|', '').replace('\n', '')
                    result_list.append(result['content'])
                result_list = result_list[column + 1:]
                for i in range(1, row - 1):  # 获取value值:list
                    value = result_list[column_start:column]
                    column_start = column
                    column = column + column_second
                    value_list.append(
                        [value[1], value[3], value[4], value[5], value[6], value[7], value[11], value[12], filename,
                         bill_number, title_type])
                key = ['uname', 'last_display_num', 'now_display_num', 'total_power', 'ele_loss', 'ele_price',
                       'ele_charge', 'uid', 'affixx_name', 'bill_number', 'title_type']
                PyMysql().insert_data('rpa_ele_billlist', key, value_list)
            except Exception as e:
                # print(e, ' ', e.__traceback__.tb_lineno)
                pass
        return True

    # 分割比例
    def OCR_type_10(data_list, filename, bill_number, title_type):
        # print(len(data_list))
        name_column = ''  # 报账点名称列
        ratio_column = ''  # 分摊比例列
        nmb_column = ''  # 电表户号列
        # print(len(data_list))
        number = 0
        for data in data_list:
            # print(data)
            try:
                number += 1
                result_list = []
                wb = openpyxl.Workbook()
                for number in range(len(data["data"]["tables"])):
                    wb.create_sheet('table' + str(number + 1))
                    sh = wb['table' + str(number + 1)]
                    for i in data["data"]["tables"][number]["cells"]:
                        sh.cell(row=i["start_row"] + 1, column=i["start_col"] + 1,
                                value=(i["content"]).replace("|", "").replace(" ", ""))
                        # print("内容：",i["content"],"行：",i["start_row"] + 1,"列：",i["start_col"] + 1)
                sh = wb['table1']
                # wb.save(r"C:\Users\liuzhiqiang2_t\Desktop\test.xlsx")
                # 处理客户地址 客户编号
                excel_code = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R',
                              'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']
                # 找出报账点名称在第几行
                data_row = 0  # 数据从第几行开始（默认：移动字段下一行）
                # 判断该图片字段位置与前面的图片是否一致
                if name_column != '' and ratio_column != '' and nmb_column != '':
                    if '名称' not in str(sh[name_column + str(1)].value).replace(' ', '').replace('\n',
                                                                                                '') or '户号' not in str(
                            sh[nmb_column + str(1)].value).replace(' ', '').replace('\n', '') or '比例' not in str(
                            sh[ratio_column + str(1)].value).replace(' ', '').replace('\n', ''):
                        name_column = ''  # 报账点名称列
                        ratio_column = ''  # 分摊比例列
                        nmb_column = ''  # 电表户号列
                # 如果坐标为空重新找位置
                if name_column == '' or ratio_column == '':
                    try:
                        for row in range(1, sh.max_row + 1):
                            for code in excel_code:
                                if sh[code + str(row)].value == None:
                                    continue
                                if '报账点名称' in str(sh[code + str(row)].value).replace(' ', '').replace('\n',
                                                                                                      '') or '基站名称' in str(
                                        sh[code + str(row)].value).replace(' ', '').replace('\n', ''):
                                    name_column = code
                                    # print('【报账点名称】字段在第',str(row),'行',str(code),'列')
                                    continue
                                if '户号' in str(sh[code + str(row)].value).replace(' ', '').replace('\n', ''):
                                    nmb_column = code
                                    # print('【户号】字段在第', str(row), '行', str(code), '列')
                                    continue
                                if '移动' in str(sh[code + str(row)].value).replace(' ', '').replace('\n', ''):
                                    ratio_column = code
                                    data_row = row + 1
                                    # print('【移动】字段在第', str(row), '行', str(code), '列')
                                    if name_column != '':
                                        break
                    except:
                        pass
                # 通过坐标找到对应字段数据
                try:
                    if (name_column == '' or ratio_column == '') and number > 1:
                        continue
                    if data_row == 0:
                        data_row = 1
                    for row in range(data_row, sh.max_row + 1):
                        name_value = ''
                        nmb_value = ''
                        ratio_value = ''
                        if sh[name_column + str(row)].value == None and sh[nmb_column + str(row)].value == None:
                            continue
                        if '报账点名称' in sh[name_column + str(row)].value or '电表户号' in sh[nmb_column + str(row)].value:
                            continue
                        if sh[name_column + str(row)].value != None:
                            name_value = str(sh[name_column + str(row)].value).replace(' ', '').replace('\n', '')
                        if sh[nmb_column + str(row)].value != None:
                            nmb_value = str(sh[nmb_column + str(row)].value).replace(' ', '').replace('\n', '')
                        if sh[ratio_column + str(row)].value != None:
                            ratio_value = str(sh[ratio_column + str(row)].value).replace(' ', '').replace('\n', '')
                        # print(name_value, nmb_value, ratio_value)
                        result_list.append([name_value, nmb_value, ratio_value, filename, bill_number])
                    key = ['site_name', 'door_no', 'split_ratio', 'affixx_name', 'bill_number']
                    PyMysql().insert_data('rpa_ocr_split_ratio', key, result_list)
                except Exception as e:
                    # print(e, ' ', str(e.__traceback__.tb_lineno))
                    pass
            except Exception as e:
                # print(e, ' ', str(e.__traceback__.tb_lineno))
                pass
            finally:
                wb.close()
        return True

    # 001 识别模板
    # def OCR_type_12(data, filename, bill_number, title_type):
    #     """
    #         新增001识别模板
    #     :param filename:
    #     :param bill_number:
    #     :param title_type:
    #     :return:
    #     """
    #     result_list = []  # 识别内容信息存储
    #     value_list = []  # 值存储
    #     column_start = 0
    #     column = data[0]['data']['tables'][0]['column']  # 获取表格列数
    #     # print(column)
    #     column_second = column
    #     row = data[0]['data']['tables'][0]['row']  # 获取表格行数
    #     result_ocr = data[0]['data']['tables'][0]['cells']  # 获取关键字结果
    #     # print(result_ocr)
    #     for result in result_ocr:  # 识别结果整理成列表
    #         result['content'] = result['content'].replace(' ', '').replace('|', '').replace('\n', '')
    #         result_list.append(result['content'])
    #     result_list = result_list[column + 1:]
    #
    #     bs = PyMysql().get_db_data("SELECT tfild_index, tfild_name " \
    #                                "FROM rpa_bill_recognition " \
    #                                "WHERE bill_name = '001表格票据' ")
    #     temp_vals = []
    #     temp_keys = []
    #     for v in bs:
    #         temp_vals.append(v.get('tfild_index'))
    #         temp_keys.append(v.get('tfild_name'))
    #     ''' key对应字段
    #        'site_name', 'uid', 'start_date', 'end_date', 'elec_meter_ratio',
    #        'last_display_num','now_display_num','ele_price','ele_loss',
    #        'total_power', 'ele_charge', 'affixx_name', 'bill_number', 'title_type'
    #     '''
    #     temp_keys.extend(['affixx_name', 'bill_number', 'title_type'])
    #     for i in range(1, row):  # 获取value值:list
    #         value = result_list[column_start:column - 1]
    #         column_start = column
    #         column = column + column_second
    #         ''' 值对应字段
    #             value[2] : 基站名称,  value[3] : 电表户号, value[4] : 缴费期始,
    #             value[5] ： 缴费期终, value[6] ： 电表倍率, value[7] ： 上期读数,
    #             value[8] ： 本期读数, value[10] ： 电费单价, value[11] : 电损电量,
    #             value[12] ： 总电量, value[13] ：电费总金额
    #         '''
    #
    #         if any(value):  # 列表中存在值，新增
    #             temp = []
    #             for i in temp_vals:
    #                 temp.append(value[i])
    #             temp.extend([filename, bill_number, title_type])
    #             value_list.append(temp)
    #     PyMysql().insert_data(PyMysql().bill_table_name, temp_keys, value_list)
    #     return True

    def OCR_type_12(data, filename, bill_number, title_type):
        """
            新增001识别模板
        :param filename:
        :param bill_number:
        :param title_type:
        :return:
        """

        recos = PyMysql().select_some('rpa_bill_recognition', 'bill_name', 'nm_111')
        if not recos:
            print('字段配置表未匹配到对应的票据列信息！')
        tlist = get_tab_fileds('fild_name', recos)

        temp_keys, temp_vals = get_tab_keys_vals('nm_111');
        all_save_data = []  # 存到数据库中的所有数据
        full_all_row_data = []

        for one_data in data:
            table_cells = one_data['data']['tables'][0]['cells']
            all_row_data = []  # 所有行的数据
            one_row_data = []  # 每一行的数据
            row_index = 0
            for cell in table_cells:
                # print("row:",cell['start_row'],", col:",cell['start_col'],", content:",cell['content'])
                cell_row_index = int(cell['start_row'])
                if cell_row_index != row_index:
                    all_row_data.append(one_row_data)
                    one_row_data = []
                    row_index += 1
                one_row_data.append(cell['content'].replace(' ', '').replace('|', '').replace('\n', ''))
            all_row_data.append(one_row_data)
            full_all_row_data.extend(all_row_data)

        lack_serial = False
        for row in full_all_row_data:
            title_flag = False
            for c in row:  # 判断行内容是否是表头
                if str(c) in tlist or str(c) == '合计':
                    title_flag = True
                    break
                if str(c) == '序号':
                    lack_serial = True

            if not title_flag:
                if any(row) and is_Not_None(row):  # 列表中存在值，新增
                    # print(row)
                    temp = []
                    for i in temp_vals:
                        try:
                            if i != None:
                                if not lack_serial:
                                    if row[i-1]:
                                        temp.append(row[i-1])
                                    else:
                                        temp.append('识别失败')
                                else:
                                    if row[i]:
                                        temp.append(row[i])
                                    else:
                                        temp.append('')

                        except IndexError:  # 程序识别少数据，自动补充为空的数据
                            temp.append('未识别补充字段')
                    if any(temp):
                        temp.extend(['nm_111',filename, bill_number, title_type])
                        # print(temp)
                        all_save_data.append(temp)
        PyMysql().insert_data(PyMysql().bill_table_name, temp_keys, all_save_data)
        return True

    """ 综合调用方法-1  """
    def OCR_Mode_001(data, filename, bill_number, title_type,bill_type_name,is_handle):
        """
        识别票据信息，存放到数据库中
        :param filename:  文件名称
        :param bill_number: 缴费单编码
        :param title_type: 缴费单类型
        :param bill_type_name: 票据类型名称
        :return:
        """
        recos = PyMysql().select_some('rpa_bill_recognition','bill_name',bill_type_name)
        if not recos:
            print('【综合调用方法-1】字段配置表未匹配到对应的票据列信息！')
        tlist  = get_tab_fileds('fild_name',recos)

        temp_keys,temp_vals = get_tab_keys_vals(bill_type_name);
        all_save_data = []  # 存到数据库中的所有数据
        month = None
        for one_data in data:
            table_cells = one_data['data']['tables'][0]['cells']
            item_cells = one_data['data']['items']
            if item_cells:
                item_cells = one_data['data']['items'][0]['content']
                month = split_titile_date(item_cells)

            all_row_data = []  # 所有行的数据
            one_row_data = []  # 每一行的数据
            row_index = 0
            for cell in table_cells:
                # print("row:",cell['start_row'],", col:",cell['start_col'],", content:",cell['content'])
                cell_row_index = int(cell['start_row'])
                if cell_row_index != row_index:
                    all_row_data.append(one_row_data)
                    one_row_data = []
                    row_index += 1
                one_row_data.append(cell['content'].replace(' ', '').replace('|', '').replace('\n', ''))
            all_row_data.append(one_row_data)
            if is_handle == 1:
                """数据处理 """
                all_row_data = getattr(OcrDataProcess, bill_type_name)(all_row_data)  # 处理数据
            for row in all_row_data:
                title_flag = False
                for c in row:  # 判断行内容是否是表头
                    if str(c) in tlist:
                        title_flag = True
                        break
                if not title_flag:
                    if any(row) and is_Not_None(row):  # 列表中存在值，新增
                        # print(row)
                        temp = []
                        for i in temp_vals:
                            try:
                                if i != None:
                                    if row[i] :
                                        temp.append(row[i])
                                    else:
                                        temp.append('识别失败')
                            except IndexError: # 程序识别少数据，自动补充为空的数据
                                temp.append('未识别补充字段')
                        if any(temp):
                            if month and None in temp_vals:
                                temp.extend([month,bill_type_name,filename, bill_number, title_type])
                            else:
                                temp.extend([bill_type_name,filename, bill_number, title_type])
                            all_save_data.append(temp)
                            print(temp)
        # PyMysql().insert_data(PyMysql().rpa_table_name, temp_keys, all_save_data)
        return True


    def OCR_Mode_002(datas, filename, bill_number, title_type, bill_type_name, is_handle):
        """
        nm_018 电费结算清单 自定义模板处理数据
        :param filename:
        :param bill_number:
        :param title_type:
        :param bill_type_name:
        :param is_handle:
        :return:
        """

        dict = get_template_datas(datas)
        start_date,end_date = get_start_end_date(dict.get('期始期终'))
        dict['期始'] = start_date
        dict['期终'] = end_date
        ele_info = dict.get('电费信息')
        ele_array = ele_info.split('金额')
        print(ele_array[2])

def get_template_datas(datas):
    """
        对应自定义模板的数据进行处理
    :param datas:
    :return:
    """
    dict = {}
    for data in datas:
        results = data['data']['results']
        for result in results:
            field_name = str(result.get('field_name')).replace('\t', '')
            vals = ''
            for val in result.get('results'):
                v = str(val).replace('\n', '').strip()
                if len(result.get('results')) > 1:
                    if v:
                        vals += v + ','
                else:
                    vals = v
            dict[field_name] = vals
    return dict

def get_start_end_date(temp_time):
    """
    期始期终的处理 格式 (2020-08-17至2020-09-18)
    :param temp_time:
    :return:
    """
    str_time = temp_time[temp_time.find('(') + 1:temp_time.find(')')]
    time_array = str_time.split('至')
    return time_array[0],time_array[1]

def get_tab_fileds(filed,data):
    '''
    根据属性名称获取属性值
    :param filed:
    :param data:
    :return:
    '''
    result = []
    for v in data:
        result.append(v[filed])
    return result

def get_tab_keys_vals(bill_name):

    sql = " SELECT tfild_index, tfild_name  FROM rpa_bill_recognition  WHERE bill_name = '"+bill_name+ "'"
    bs = PyMysql().get_db_data(sql)
    temp_vals = []
    temp_keys = []
    for v in bs:
        temp_vals.append(v.get('tfild_index'))
        temp_keys.append(v.get('tfild_name'))
    temp_keys.extend(['bill_type_name','affixx_name', 'bill_number', 'title_type'])
    return  temp_keys,temp_vals

def format_time(publish_Time):
    """
        转换日期 , 时间转换格式：u"%Y年%m月"
    """
    array = time.strptime(publish_Time, u"%Y年%m月")
    try:
        publishTime = time.strftime("%Y-%m", array)
    except Exception as e:
        print(e)
    return publishTime

def split_titile_date(t):
    '''
        判断表格中标题是否包含时间数据，如果包含返回处理后的年月
        :param t:
        :return:
    '''
    str = None
    if  all(f in t for f in ['2','年','月']):
        index = t.find('2')
        m_index = t.find('月')
        str = t[index:m_index+1]
        # str = format_time(str)
    return str


def is_Not_None(array):
    """
    判断识别的数据是否为特定的不需要的数据，如果是，不处理该数据
    :param array:
    :return:
    """
    for a in array:
        if a not in [None,'A','-','','未识别补充字段']:
            return True
    return False
